package com.nix.web.databases.jdbc.word;

import com.nix.web.databases.jdbc.JdbcDao;
import com.nix.web.databases.jdbc.core.JdbcDataSources;
import com.nix.web.databases.pojo.word.Category;
import com.nix.web.databases.pojo.word.Word;
import com.nix.web.databases.service.iteratory.Iterator;
import com.nix.web.service.word.CategoryServiceImpl;
import com.nix.web.service.word.WordServiceImpl;
import org.apache.log4j.Logger;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Map;
import java.util.concurrent.CopyOnWriteArrayList;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.Executors;


/**
 * 当导入数据 大于1W小于10W时候使用 批量+事物
 * Create by zhangpe0312@qq.com on 2017/12/21.
 */
@Service
public class BatchAndTransactionToWord implements JdbcDao<Word> {

    //暂定一100个线程运行
    private ExecutorService pool = Executors.newFixedThreadPool(100);

    @Autowired
    private JdbcDataSources dataSources;

    private static final Logger log = Logger.getLogger(BatchAndTransactionToWord.class);

    private Map<String, Integer> keepCategory = new HashMap<String, Integer>();

    @Autowired
    private CategoryServiceImpl categoryService;

    @Autowired
    private WordServiceImpl wordService;

    @Override
    public void bachAdd(Iterator iterator, Integer groupNumber) {

        //强制转换为我需要的类型的迭代器
        Iterator<Word> myIterator = (Iterator<Word>) iterator;
        String insert = " INSERT INTO" +
                " word_word(NAME,wordmeaning,pinyin,frequency)" +
                " VALUES(?,?,?,?)";
        Connection connection = null;
        PreparedStatement commit = null;

        try {
            connection = dataSources.getDataSource().getConnection();
            //设置手动提交
            connection.setAutoCommit(false);

            commit = connection.prepareStatement(insert);

            ArrayList<Word> words = new ArrayList<Word>();

            int i;
            for (i = 0; iterator.hasNext(); i++) {
                Word word = myIterator.next();

                if (word == null || word.getName().equals(""))
                    continue;

                words.add(word);

                //向数据库中导入单词
                commit.setString(1, word.getName());
                commit.setString(2, word.getWordMeaning());
                commit.setString(3, word.getPinYin());
                commit.setInt(4, word.getFrequency());

                commit.addBatch();

                //每group个单词刷新一次提交
                if (i % groupNumber == 0 && i != 0) {
                    try {
                        commit.executeBatch();
                    } catch (Exception e) {
                        System.out.println("提交出错 - 1  " + "pin = " + word.getPinYin() + e);
                    }
                }
            }

            try {
                commit.executeBatch();
            } catch (Exception e) {
                System.out.println("提交出错 2 - " + e);
            }
            connection.commit();


            ArrayList<Word> databasesWord = (ArrayList<Word>) wordService.selectAll();


            for (int j = 1; j < databasesWord.size(); j++) {
                words.get(j).setId(databasesWord.get(j).getId());
            }

            batchAddCategory(words);

        } catch (Exception e) {
            log.error("添加单词出错 " + e);
            e.printStackTrace();
        } finally {
            try {
                commit.close();
                connection.close();
            } catch (SQLException e) {
                System.out.println("关闭 - PreparedStatement - 或者关闭 - Connection - 失败！ " + e);
            }
        }
    }

    /**
     * 批量添加词性
     *
     * @param words
     */
    public void batchAddCategory(ArrayList<Word> words) {
        String insert = " INSERT INTO" +
                " word_word_category(category_id,word_id)" +
                " VALUES(?,?)";
        Connection connection;
        PreparedStatement commit;

        try {
            connection = dataSources.getDataSource().getConnection();
            //设置手动提交
            connection.setAutoCommit(false);

            commit = connection.prepareStatement(insert);

            for (int i = 0; i < words.size(); i++) {


                Word word = words.get(i);

                //首先检测这个词性再map中是否含有
                try {
                    int id = keepCategory.get(word.getCategory().getMark());

                    //想数据库中绑定单词和词性关系
                    commit.setInt(1, id);
                    commit.setInt(2, word.getId());
                    commit.addBatch();

                } catch (Exception e) {
                    log.error("缓存中没有这个词性 " + e);

                    //查找数据库中是否有这个词性
                    try {
                        Category category = categoryService.selectByName(word.getCategory().getMark());
                        keepCategory.put(category.getMark(), category.getId());
                        //想数据库中绑定单词和词性关系
                        commit.setInt(1, category.getId());
                        commit.setInt(2, word.getId());
                        commit.addBatch();
                    } catch (Exception e1) {
                        e1.printStackTrace();
                        log.error("在数据中没有找到这个词性 " + e1);

                        //先向数据空导入这个词性
                        //如果数据库没有，那么先添加进入数据库再绑定
                        try {
                            categoryService.add(word.getCategory());
                            keepCategory.put(word.getCategory().getMark(), word.getCategory().getId());

                            //想数据库中绑定单词和词性关系
                            commit.setInt(1, word.getCategory().getId());
                            commit.setInt(2, word.getId());
                            commit.addBatch();

                        } catch (Exception e2) {
                            e2.printStackTrace();
                            log.error("向数据空中添加词性失败 " + e2);
                        }

                    }
                }


                //每group个单词刷新一次提交
                if (i % 3000 == 0 && i != 0) {
                    try {
                        commit.executeBatch();
                    } catch (Exception e) {
                        log.error("添加词性出错 " + e);
                    }
                }
            }

            try {
                commit.executeBatch();
            } catch (Exception e) {
                System.out.println("提交出错 2 - " + e);
            }
            connection.commit();

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}
